package com.example.musicplayer.dao

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.util.Log
import com.example.musicplayer.model.UserProfile
import com.example.musicplayer.util.UserProfileDatabaseHelper

class UserProfileDAO(context: Context) {
    private val dbHelper = UserProfileDatabaseHelper(context)
    private val readableDatabase: SQLiteDatabase = dbHelper.readableDatabase
    private val writableDatabase: SQLiteDatabase = dbHelper.writableDatabase

    fun insertUserProfile(userProfile: UserProfile): Boolean {
        val values = ContentValues().apply {
            put(UserProfileDatabaseHelper.COLUMN_USERNAME, userProfile.username)
            put(UserProfileDatabaseHelper.COLUMN_BIO, userProfile.bio)
            put(UserProfileDatabaseHelper.COLUMN_PHONE, userProfile.phone)
            put(UserProfileDatabaseHelper.COLUMN_GENDER, userProfile.gender)
            put(UserProfileDatabaseHelper.COLUMN_BIRTHDAY, userProfile.birthday)
            put(UserProfileDatabaseHelper.COLUMN_LOCATION, userProfile.location)
            put(UserProfileDatabaseHelper.COLUMN_SCHOOL, userProfile.school)
            userProfile.image?.let { put(UserProfileDatabaseHelper.COLUMN_IMAGE, it) } // 处理 image 字段
            put(UserProfileDatabaseHelper.COLUMN_PASSWORD, userProfile.password)
        }

        val result = writableDatabase.insert(UserProfileDatabaseHelper.TABLE_USER_PROFILE, null, values)
        return result != -1L
    }

    fun updateUserProfile(userProfile: UserProfile) {
        val values = ContentValues().apply {
            put(UserProfileDatabaseHelper.COLUMN_USERNAME, userProfile.username)
            put(UserProfileDatabaseHelper.COLUMN_BIO, userProfile.bio)
            put(UserProfileDatabaseHelper.COLUMN_PHONE, userProfile.phone)
            put(UserProfileDatabaseHelper.COLUMN_GENDER, userProfile.gender)
            put(UserProfileDatabaseHelper.COLUMN_BIRTHDAY, userProfile.birthday)
            put(UserProfileDatabaseHelper.COLUMN_LOCATION, userProfile.location)
            put(UserProfileDatabaseHelper.COLUMN_SCHOOL, userProfile.school)
            userProfile.image?.let { put(UserProfileDatabaseHelper.COLUMN_IMAGE, it) } // 处理 image 字段
            if (userProfile.password != null) {
                put(UserProfileDatabaseHelper.COLUMN_PASSWORD, userProfile.password)
            }
        }
        writableDatabase.update(
            UserProfileDatabaseHelper.TABLE_USER_PROFILE,
            values,
            "${UserProfileDatabaseHelper.COLUMN_ID} = ?",
            arrayOf(userProfile.id.toString())
        )
    }

    @SuppressLint("Range")
    fun getUserProfile(userId: Int): UserProfile? {
        val cursor: Cursor = readableDatabase.query(
            UserProfileDatabaseHelper.TABLE_USER_PROFILE,
            null,
            "${UserProfileDatabaseHelper.COLUMN_ID} = ?",
            arrayOf(userId.toString()),
            null,
            null,
            null
        )
        return if (cursor.moveToFirst()) {
            val id = cursor.getInt(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_ID))
            val username = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_USERNAME))
            val bio = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_BIO))
            val phone = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_PHONE))
            val gender = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_GENDER))
            val birthday = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_BIRTHDAY))
            val location = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_LOCATION))
            val school = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_SCHOOL))
            val image = cursor.getBlob(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_IMAGE)) // 读取 image 字段
            val password = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_PASSWORD))

            UserProfile(
                id = id,
                username = username,
                bio = bio,
                phone = phone,
                gender = gender,
                birthday = birthday,
                location = location,
                school = school,
                image = image,
                password = password
            )
        } else {
            null
        }.also {
            cursor.close()
        }
    }

    @SuppressLint("Range")
    fun getUserProfileByUsernameAndPassword(username: String, password: String): UserProfile? {
        val cursor: Cursor = readableDatabase.query(
            UserProfileDatabaseHelper.TABLE_USER_PROFILE,
            null,
            "${UserProfileDatabaseHelper.COLUMN_USERNAME} = ? AND ${UserProfileDatabaseHelper.COLUMN_PASSWORD} = ?",
            arrayOf(username, password),
            null,
            null,
            null
        )
        return if (cursor.moveToFirst()) {
            val id = cursor.getInt(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_ID))
            val username = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_USERNAME))
            val bio = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_BIO))
            val phone = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_PHONE))
            val gender = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_GENDER))
            val birthday = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_BIRTHDAY))
            val location = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_LOCATION))
            val school = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_SCHOOL))
            val image = cursor.getBlob(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_IMAGE)) // 读取 image 字段
            val password = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_PASSWORD))

            UserProfile(
                id = id,
                username = username,
                bio = bio,
                phone = phone,
                gender = gender,
                birthday = birthday,
                location = location,
                school = school,
                image = image,
                password = password
            )
        } else {
            null
        }.also {
            cursor.close()
        }
    }

    @SuppressLint("Range")
    fun getUserProfileById(userId: Int): UserProfile? {
        val cursor: Cursor = readableDatabase.query(
            UserProfileDatabaseHelper.TABLE_USER_PROFILE,
            null,
            "${UserProfileDatabaseHelper.COLUMN_ID} = ?",
            arrayOf(userId.toString()),
            null,
            null,
            null
        )
        return if (cursor.moveToFirst()) {
            val id = cursor.getInt(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_ID))
            val username = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_USERNAME))
            val bio = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_BIO))
            val phone = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_PHONE))
            val gender = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_GENDER))
            val birthday = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_BIRTHDAY))
            val location = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_LOCATION))
            val school = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_SCHOOL))
            val image = cursor.getBlob(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_IMAGE)) // 读取 image 字段
            val password = cursor.getString(cursor.getColumnIndex(UserProfileDatabaseHelper.COLUMN_PASSWORD))

            UserProfile(
                id = id,
                username = username,
                bio = bio,
                phone = phone,
                gender = gender,
                birthday = birthday,
                location = location,
                school = school,
                image = image,
                password = password
            )
        } else {
            null
        }.also {
            cursor.close()
            Log.d("UserProfileDAO", "Query result for user ID $userId: ${it?.username ?: "Not found"}")
        }
    }
}